package com.littlepay.kr4.paulin.dfeba77.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;

import com.littlepay.kr4.paulin.dfeba77.entity.Sticker;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * Created by 77dfeba on 2017/5/11.
 * sqlite工具
 * 可执行查询、插入、
 * 未完成：删除
 */

public class SqliteHelper extends SQLiteOpenHelper {
    private SQLiteDatabase db;
    private static final int FIRST_SCHEMA_VERSION = 1;
    private int SCHEMA_VERSION;
    public static int minID = 1000;//sqlite最小ID为1
    public static int currentLength;

    public SqliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.SCHEMA_VERSION = version;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //数据库表格属性为 id，花费，属性，详情（大部分为空），日期
        //id用读取检索
        //TODO:解决记入账目有小数点问题，cost字段用INT，存入前*100取出/100即可
        db.execSQL("CREATE TABLE stick(_id INTEGER PRIMARY KEY AUTOINCREMENT, cost INT, attr INT, detail TEXT, date TEXT)");
}

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (SCHEMA_VERSION == 2 && FIRST_SCHEMA_VERSION == 1) {
            //db.execSQL("DROP TABLE "+ SqliteConfig.SQLITE_DATABASE_FILE_NAME +"."+"stick");
            System.out.println("update!");
        }
    }

    public String getCost(Cursor c) {
        return (c.getString(1));
    }

    public String getAttr(Cursor c) {
        return (c.getString(2));
    }

    public String getDetail(Cursor c) {
        return (c.getString(3));
    }

    public String getdate(Cursor c) {
        return (c.getString(4));
    }

    /**
     * 每次插入sqlite操作，
     * 存储只存储简单数据属性
     * 读取展示需要变成文字和对应icon
     */

    public long insertData(int cost, int atrr, @Nullable String detail, String date) {
        SqliteHelper myHelper = this;
        long result = -1;
        //获取数据库对象
        SQLiteDatabase db = myHelper.getWritableDatabase();
        //使用execSQL方法向表中插入数据
        //db.execSQL("insert into hero_info(name,level) values('bb',0)");
        //使用insert方法向表中插入数据
        ContentValues values = new ContentValues();
        int iCost, iAttr;
        String iDate;
        iCost = cost;
        iAttr = atrr;
        //todo:detail为空是否会引起异常
        if (detail == null) {
            detail = "";
        }
        iDate = date;
        values.put("cost", iCost);
        values.put("attr", iAttr);
        //System.out.println(iDate);
        values.put("detail", detail);
        values.put("date", iDate);
        //调用方法插入数据
        //TODO:insert的返回值是什么
        String uid = PrefUtils.getUid();
        result = db.insert("stick", "_id", values);
        System.out.println(result);
        //清空ContentValues对象
        values.clear();
        //关闭SQLiteDatabase对象
        db.close();
        //返回值是最新插入数据的ID
        return result;
    }

    /**
     * 云端插入数据
     *
     * @param id
     * @param cost
     * @param atrr
     * @param detail
     * @param date
     * @return
     */
    public long insertDataFromCloud(int id, int cost, int atrr, @Nullable String detail, String date) {
        SqliteHelper myHelper = this;
        long result = -1;
        //获取数据库对象
        SQLiteDatabase db = myHelper.getWritableDatabase();
        //使用execSQL方法向表中插入数据
        //db.execSQL("insert into hero_info(name,level) values('bb',0)");
        //使用insert方法向表中插入数据
        ContentValues values = new ContentValues();
        int iCost, iAttr, iid;
        String iDate;
        iid = id;
        iCost = cost;
        iAttr = atrr;
        //todo:detail为空是否会引起异常
        if (detail == null) {
            detail = "";
        }
        iDate = date;
        values.put("_id", iid);
        values.put("cost", iCost);
        values.put("attr", iAttr);
        //System.out.println(iDate);
        values.put("detail", detail);
        values.put("date", iDate);
        //调用方法插入数据
        //TODO:insert的返回值是什么
        result = db.insert("stick", "_id", values);
        System.out.println(result);
        //清空ContentValues对象
        values.clear();
        //关闭SQLiteDatabase对象
        db.close();
        //返回值是最新插入数据的ID
        return result;
    }

    /**
     * queryDataList获取数据库的 按天查询 的结果
     *
     * @param queryString 为空时默认返回近一天的数据，此后每次拉取一天的
     * @return 返回所有结果集的List，可能没有数据，但是返回值永不为空
     */
    public List<Sticker> queryDataListByDay(@Nullable String queryString) {
        SqliteHelper myHelper = this;
        //为空默认返回今日的所有列表
        if (queryString == null) {
            queryString = "SELECT * FROM stick WHERE date GLOB '" + DateUtil.getTodayDate() + "*'";
        } else {
            queryString = "SELECT * FROM stick WHERE date GLOB '" + queryString + "*'";
        }
        List<Sticker> stickerList = new ArrayList<>();
        //查询表中的数据
        Cursor cursor = queryData(queryString);
        //获取所有列的索引
        int idIndex = cursor.getColumnIndex("_id");
        int costIndex = cursor.getColumnIndex("cost");
        int attrIndex = cursor.getColumnIndex("attr");
        int detailIndex = cursor.getColumnIndex("detail");
        int dateIndex = cursor.getColumnIndex("date");
        //获取本次加载的数据的长度，本日结果没有就0，不显示，从而控制viewType
        currentLength = cursor.getCount();
        //先让游标到最前的数据，该游标表示总共次获取的最低的ID值
        if (cursor.moveToFirst()) {
            int newMinID = cursor.getInt(idIndex);
            for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
                newMinID = newMinID < cursor.getInt(idIndex) ? newMinID : cursor.getInt(idIndex);
            }
            //minID = minID < cursor.getInt(idIndex)?minID:cursor.getInt(idIndex);
            minID = minID < newMinID ? minID : newMinID;
        }
        //判断条件 从cursor游标的结果集的First每一次moveToNext直到到达最后一个
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            Sticker sticker = new Sticker();
            //get类型不能出现错误
            sticker.setCost(cursor.getInt(costIndex) * 0.01);
            sticker.setAttribution(cursor.getInt(attrIndex));
            //详情为空默认返回""或者null需要判断处理
            String detail;
            if (Objects.equals(cursor.getString(detailIndex), "") || cursor.getString(detailIndex) == null) {
                detail = "";
            } else {
                detail = cursor.getString(detailIndex);
            }
            sticker.setDetail(detail);
            sticker.setDate(cursor.getString(dateIndex));
            stickerList.add(sticker);
        }

        //System.out.println(result);
        cursor.close();//关闭结果集
        db.close();//关闭数据库对象
        return stickerList;
    }

    /**
     * 按照月份访问数据库
     *
     * @param queryStringMonth 访问数据库月份日期
     * @return 返回所有结果集的List<Double>，可能没有数据，但是返回值永不为空
     */
    public List<Sticker> queryDataListByMonth(String queryStringMonth) {
        //List<Double> monthCostList = new ArrayList<>();
        queryStringMonth = "SELECT * FROM stick WHERE date GLOB '" + queryStringMonth + "*'";
        Cursor cursor = queryData(queryStringMonth);
        int idIndex = cursor.getColumnIndex("_id");
        int costIndex = cursor.getColumnIndex("cost");
        int attrIndex = cursor.getColumnIndex("attr");
        int detailIndex = cursor.getColumnIndex("detail");
        int dateIndex = cursor.getColumnIndex("date");
        List<Sticker> totalMonthList = new ArrayList<>();
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            //monthCostList.add(cursor.getInt(costIndex) * 0.01);
            //totalMonthList += cursor.getInt(costIndex) * 0.01
            Sticker sticker = new Sticker();
            sticker.setCost(cursor.getInt(costIndex) * 0.01);
            sticker.setAttribution(cursor.getInt(attrIndex));
            totalMonthList.add(sticker);
        }
        return totalMonthList;
    }

    /**
     * 按照月份访问数据库
     *
     * @param queryString 访问数据库执行语句
     * @return 返回所有结果集，可能没有数据，但是返回值永不为空
     */
    private Cursor queryData(String queryString) {
        SqliteHelper myHelper = this;
        //获得数据库对象
        db = myHelper.getReadableDatabase();
        //查询表中的数据
        return db.rawQuery(queryString, null);
    }

    public int numOfDataBase() {
        SqliteHelper myHelper = this;
        //以只读的方式打开数据库
        SQLiteDatabase db = myHelper.getReadableDatabase();
        //查询数据库中的所有数据
        Cursor cursor = db.query("stick", null, null, null, null, null, "_id asc");
        //记录数据库中，数据数量
        int num = 0;
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            num++;
        }
        cursor.close();
        db.close();
        return num;
    }


}
